package cn.lili.modules.goods.serviceimpl;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.lili.common.enums.ResultCode;
import cn.lili.common.exception.ServiceException;
import cn.lili.common.security.enums.SceneEnums;
import cn.lili.modules.goods.entity.dos.Category;
import cn.lili.modules.goods.entity.dos.GoodsUnit;
import cn.lili.modules.goods.entity.dto.GoodsImportDTO;
import cn.lili.modules.goods.entity.dto.GoodsOperationDTO;
import cn.lili.modules.goods.entity.dto.WholesaleDTO;
import cn.lili.modules.goods.entity.enums.PurchaseRuleEnum;
import cn.lili.modules.goods.entity.vos.CategoryVO;
import cn.lili.modules.goods.integration.GoodsIntegrationService;
import cn.lili.modules.goods.service.CategoryService;
import cn.lili.modules.goods.service.GoodsImportService;
import cn.lili.modules.goods.service.GoodsUnitService;
import cn.lili.modules.store.client.FreightTemplateClient;
import cn.lili.modules.store.client.StoreClient;
import cn.lili.modules.store.entity.vos.FreightTemplateVO;
import cn.lili.modules.supplier.entity.dto.SupplierGoodsOperationDTO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@Service
@RequiredArgsConstructor
public class GoodsImportServiceImpl implements GoodsImportService {


    private final FreightTemplateClient freightTemplateClient;


    private final StoreClient storeClient;

    private final CategoryService categoryService;

    private final GoodsUnitService goodsUnitService;

    private final GoodsIntegrationService goodsIntegrationService;

    private final List<String> goodsImportCells = new ArrayList<>(List.of(
            "商品名称", "商品类型", "商品卖点", "商品分类", "运费模板", "计量单位", "发布状态", "商品图片(多个,分割)", "成本价", "销售价", "库存", "重量", "货号", "详情", "规格项", "规格值"));

    private final int[] getGoodsImportColumnWidth = new int[]
            {7000, 7000, 7000, 7000, 7000, 7000, 3000, 7000, 3000, 3000, 3000, 3000, 7000, 7000, 3000, 3000};

    private final List<String> goodsImportCellsSupplier = new ArrayList<>(List.of(
            "商品名称", "商品类型", "是否支持代发", "是否支持采购", "采购规则", "商品卖点", "商品分类", "运费模板", "计量单位", "发布状态", "商品图片(多个,分割)", "采购规则(10-20,表示当商品购买数量 ≥10 时，售价为 ￥20 /个。多个,隔开,最多3个)", "成本价(采购无需填写)", "销售价(采购无需填写)", "库存", "重量", "货号", "详情", "规格项", "规格值"));

    private final int[] getGoodsImportColumnWidthSupplier = new int[]
            {7000, 7000, 3000, 3000,3000, 7000, 7000, 7000, 7000, 3000, 7000, 14000, 3000, 3000, 3000, 3000, 7000, 7000, 3000, 3000};

    @Override
    public void download(HttpServletResponse response, String storeId, String scene) {

        //创建Excel工作薄对象
        Workbook workbook;
        if (SceneEnums.SUPPLIER.name().equals(scene)) {
            workbook = createSupplierGoodsRow();
        } else {
            workbook = createGoodsRow();
        }

        String businessCategory = storeClient.getStore(storeId).getBusinessCategory();
        List<CategoryVO> categoryVOList = this.categoryService.getStoreCategory(businessCategory.split(","));
        List<String> categoryNameList = new ArrayList<>();

        //先简单写，后期优化
        //循环三次添加值
        //循环列表，存放ID-分类名称
        for (CategoryVO categoryVO1 : categoryVOList) {
            for (CategoryVO categoryVO2 : categoryVO1.getChildren()) {
                for (CategoryVO categoryVO3 : categoryVO2.getChildren()) {
                    categoryNameList.add(categoryVO3.getId() + "-" + categoryVO3.getName());
                }
            }
        }

        List<String> freightTemplateNameList = new ArrayList<>();
        //循环列表，存放ID-运费模板名称
        for (FreightTemplateVO freightTemplateVO : freightTemplateClient.getFreightTemplateList(storeId)) {
            freightTemplateNameList.add(freightTemplateVO.getId() + "-" + freightTemplateVO.getName());
        }

        //获取计量单位
        List<String> goodsUnitList = new ArrayList<>();
        List<GoodsUnit> goodsUnitListVO = goodsUnitService.list();
        for (GoodsUnit goodsUnit : goodsUnitListVO) {
            goodsUnitList.add(goodsUnit.getId() + "-" + goodsUnit.getName());
        }

        //添加商品类型
        int goodsTypeColNum = getCellIndex("商品类型", scene);
        this.excelTo255(workbook, "hiddenGoodsType", 1, new String[]{"实物商品", "虚拟商品"}, 1, 5000, goodsTypeColNum, goodsTypeColNum);

        //添加分类
        int categoryColNum = getCellIndex("商品分类", scene);
        this.excelTo255(workbook, "hiddenCategoryVO", 2, categoryNameList.toArray(new String[]{}), 1, 5000, categoryColNum, categoryColNum);

        //添加运费模板
        int freightTemplateColNum = getCellIndex("运费模板", scene);
        this.excelTo255(workbook, "hiddenFreightTemplateVO", 3, freightTemplateNameList.toArray(new String[]{}), 1, 5000, freightTemplateColNum, freightTemplateColNum);

        //添加计量单位
        int goodsUnitColNum = getCellIndex("计量单位", scene);
        this.excelTo255(workbook, "hiddenGoodsUnit", 4, goodsUnitList.toArray(new String[]{}), 1, 5000, goodsUnitColNum, goodsUnitColNum);

        //添加发布状态
        int releaseColNum = getCellIndex("发布状态", scene);
        this.excelTo255(workbook, "hiddenRelease", 5, new String[]{"上架", "仓库中"}, 1, 5000, releaseColNum, releaseColNum);

        if (SceneEnums.SUPPLIER.name().equals(scene)) {
            int hiddenPurchaseRuleColNum = goodsImportCellsSupplier.indexOf("采购规则");
            this.excelTo255(workbook, "hiddenPurchaseRule", 6, new String[]{"SKU", "SPU"}, 1, 5000, hiddenPurchaseRuleColNum, hiddenPurchaseRuleColNum);
            int hiddenProxyColNum = goodsImportCellsSupplier.indexOf("是否支持代发");
            this.excelTo255(workbook, "hiddenProxy", 7, new String[]{"是", "否"}, 1, 5000, hiddenProxyColNum, hiddenProxyColNum);
            int hiddenPurchaseColNum = goodsImportCellsSupplier.indexOf("是否支持采购");
            this.excelTo255(workbook, "hiddenPurchase", 8, new String[]{"是", "否"}, 1, 5000, hiddenPurchaseColNum, hiddenPurchaseColNum);
        }

        ServletOutputStream out = null;
        try {
            //设置公共属性，列表名称
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("下载商品导入模板", StandardCharsets.UTF_8) + ".xls");
            out = response.getOutputStream();
            workbook.write(out);
        } catch (Exception e) {
            log.error("下载商品导入模板错误", e);
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private int getCellIndex(String cellName, String scene) {
        return SceneEnums.SUPPLIER.name().equals(scene) ? goodsImportCellsSupplier.indexOf(cellName) : goodsImportCells.indexOf(cellName);
    }

    private Workbook createGoodsRow() {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("商品导入模板");
        Row row = sheet.createRow(0);
        for (int i = 0; i < goodsImportCells.size(); i++) {
            row.createCell(i).setCellValue(goodsImportCells.get(i));
        }
        for (int i = 0; i < getGoodsImportColumnWidth.length; i++) {
            sheet.setColumnWidth(i, getGoodsImportColumnWidth[i]);
        }
        return workbook;
    }

    private Workbook createSupplierGoodsRow() {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("供应商商品导入模板");
        Row row = sheet.createRow(0);
        for (int i = 0; i < goodsImportCellsSupplier.size(); i++) {
            row.createCell(i).setCellValue(goodsImportCellsSupplier.get(i));
        }
        for (int i = 0; i < getGoodsImportColumnWidthSupplier.length; i++) {
            sheet.setColumnWidth(i, getGoodsImportColumnWidthSupplier[i]);
        }
        return workbook;
    }

    @Override
    public void importExcel(MultipartFile files, String scene) throws Exception {
        InputStream inputStream;
        List<GoodsImportDTO> goodsImportDTOList = new ArrayList<>();

        inputStream = files.getInputStream();
        ExcelReader excelReader = ExcelUtil.getReader(inputStream);

        // 读取列表
        // 检测数据-查看分类、模板、计量单位是否存在
        List<List<Object>> read = excelReader.read(1, excelReader.getRowCount());
        for (List<Object> objects : read) {
            GoodsImportDTO goodsImportDTO = new GoodsImportDTO();

            int categoryColNum = getCellIndex("商品分类", scene);
            String categoryId = objects.get(categoryColNum).toString().substring(0, objects.get(categoryColNum).toString().indexOf("-"));

            Category category = categoryService.getCategoryById(categoryId);
            if (category == null) {
                throw new ServiceException("商品分类不存在：" + objects.get(categoryColNum).toString().substring(objects.get(categoryColNum).toString().indexOf("-")));
            }
            goodsImportDTO.setCategory(category);


            int templateColNum = getCellIndex("运费模板", scene);
            String templateId = objects.get(templateColNum).toString().substring(0, objects.get(templateColNum).toString().indexOf("-"));
            FreightTemplateVO freightTemplateVO = freightTemplateClient.getFreightTemplate(templateId);
            if (freightTemplateVO == null) {
                throw new ServiceException("配送模板不存在：" + objects.get(templateColNum).toString().substring(objects.get(templateColNum).toString().indexOf("-")));
            }
            goodsImportDTO.setTemplate(templateId);

            int goodsNameColNum = getCellIndex("商品名称", scene);
            if (CharSequenceUtil.isEmpty(objects.get(goodsNameColNum).toString())) {
                throw new ServiceException(ResultCode.IMPORT_GOODS_NAME_ERROR);
            }
            goodsImportDTO.setGoodsName(objects.get(goodsNameColNum).toString());

            int sellingPointColNum = getCellIndex("商品卖点", scene);
            goodsImportDTO.setSellingPoint(objects.get(sellingPointColNum).toString());

            int goodsTypeColNum = getCellIndex("商品类型", scene);
            goodsImportDTO.setGoodsType(objects.get(goodsTypeColNum).toString().equals("实物商品") ? "PHYSICAL_GOODS" : "VIRTUAL_GOODS");

            int goodsUnitColNum = getCellIndex("计量单位", scene);
            goodsImportDTO.setGoodsUnit(objects.get(goodsUnitColNum).toString().substring(objects.get(goodsUnitColNum).toString().indexOf("-") + 1));

            int releaseColNum = getCellIndex("发布状态", scene);
            goodsImportDTO.setRelease(objects.get(releaseColNum).toString().equals("上架"));

            int imagesColNum = getCellIndex("商品图片(多个,分割)", scene);
            if (CharSequenceUtil.isEmpty(objects.get(imagesColNum).toString())) {
                throw new ServiceException(ResultCode.IMPORT_GOODS_IMAGE_ERROR);
            }
            List<Map<String, String>> images = new ArrayList<>();
            List<String> goodsGalleryList = new ArrayList<>();
            for (String url : objects.get(imagesColNum).toString().split(",")) {
                Map<String, String> map = new HashMap<>();
                map.put("url", url);
                images.add(map);
                goodsGalleryList.add(url);
            }
            goodsImportDTO.setImages(images);
            goodsImportDTO.setGoodsGalleryList(goodsGalleryList);


            int quantityColNum = getCellIndex("库存", scene);
            goodsImportDTO.setQuantity(Convert.toInt(objects.get(quantityColNum)));

            int weightColNum = getCellIndex("重量", scene);
            goodsImportDTO.setWeight(Convert.toDouble(objects.get(weightColNum)));

            int snColNum = getCellIndex("货号", scene);
            goodsImportDTO.setSn(objects.get(snColNum).toString());

            int introColNum = getCellIndex("详情", scene);
            goodsImportDTO.setIntro("<p>" + objects.get(introColNum).toString() + "</p>");

            int skuKeyColNum = getCellIndex("规格项", scene);
            goodsImportDTO.setSkuKey(objects.get(skuKeyColNum).toString());

            int skuValueColNum = getCellIndex("规格值", scene);
            goodsImportDTO.setSkuValue(objects.get(skuValueColNum).toString());

            if (SceneEnums.SUPPLIER.name().equals(scene)) {
                int hiddenPurchaseRuleColNum = goodsImportCellsSupplier.indexOf("采购规则");
                goodsImportDTO.setPurchaseRule("SPU".equals(objects.get(hiddenPurchaseRuleColNum).toString()) ? PurchaseRuleEnum.SPU : PurchaseRuleEnum.SKU);

                int hiddenProxyColNum = goodsImportCellsSupplier.indexOf("是否支持代发");
                goodsImportDTO.setSupportProxy(objects.get(hiddenProxyColNum).toString().equals("是"));

                int hiddenPurchaseColNum = goodsImportCellsSupplier.indexOf("是否支持采购");
                goodsImportDTO.setSupportPurchase(objects.get(hiddenPurchaseColNum).toString().equals("是"));

                int purchaseRulesColNum = goodsImportCellsSupplier.indexOf("采购规则(10-20,表示当商品购买数量 ≥10 时，售价为 ￥20 /个。多个,隔开,最多3个)");
                if (CharSequenceUtil.isNotEmpty(objects.get(purchaseRulesColNum).toString())) {
                    List<WholesaleDTO> wholesaleList = new ArrayList<>();
                    for (String rule : objects.get(purchaseRulesColNum).toString().split(",")) {
                        WholesaleDTO wholesaleDTO = new WholesaleDTO();
                        wholesaleDTO.setNum(Convert.toInt(rule.substring(0, rule.indexOf("-"))));
                        wholesaleDTO.setPrice(Convert.toDouble(rule.substring(rule.indexOf("-") + 1)));
                        wholesaleList.add(wholesaleDTO);
                    }
                    goodsImportDTO.setWholesaleList(wholesaleList);
                }

                int costColNum = getCellIndex("成本价(采购无需填写)", scene);
                int priceColNum = getCellIndex("销售价(采购无需填写)", scene);
                if (CharSequenceUtil.isEmpty(objects.get(purchaseRulesColNum).toString())) {
                    goodsImportDTO.setCost(Convert.toDouble(objects.get(costColNum)));
                    goodsImportDTO.setPrice(Convert.toDouble(objects.get(priceColNum)));
                }

            } else {
                int costColNum = getCellIndex("成本价", scene);
                goodsImportDTO.setCost(Convert.toDouble(objects.get(costColNum)));

                int priceColNum = getCellIndex("销售价", scene);
                goodsImportDTO.setPrice(Convert.toDouble(objects.get(priceColNum)));
            }
            goodsImportDTOList.add(goodsImportDTO);
        }
        //添加商品
        if (SceneEnums.SUPPLIER.name().equals(scene)) {
            addSupplierGoodsList(goodsImportDTOList);
        } else {
            addGoodsList(goodsImportDTOList);
        }

    }

    /**
     * 添加商品
     *
     * @param goodsImportDTOList 商品列表
     */
    private void addGoodsList(List<GoodsImportDTO> goodsImportDTOList) {
        Map<String, List<GoodsImportDTO>> collect = goodsImportDTOList.stream().collect(Collectors.groupingBy(GoodsImportDTO::getGoodsName));
        for (Map.Entry<String, List<GoodsImportDTO>> entry : collect.entrySet()) {
            if (!entry.getValue().isEmpty()) {
                GoodsImportDTO goodsImportDTO = entry.getValue().get(0);
                GoodsOperationDTO goodsOperationDTO = new GoodsOperationDTO(entry.getValue().get(0));
                List<Map<String, Object>> skuList = new ArrayList<>();
                for (GoodsImportDTO goodsImportDTOSku : entry.getValue()) {
                    Map<String, Object> skuMap = new HashMap<>();
                    if (!skuList.isEmpty() && skuList.stream().noneMatch(sku -> sku.containsKey(goodsImportDTOSku.getSkuKey()))) {
                        for (Map<String, Object> map : skuList) {
                            map.keySet().stream().filter(key -> !key.equals("quantity") && !key.equals("sn") && !key.equals("weight") && !key.equals("cost") && !key.equals("images")).forEach(key -> skuMap.put(key, map.get(key)));
                            map.put(goodsImportDTOSku.getSkuKey(), goodsImportDTOSku.getSkuValue());
                        }
                    }
                    skuMap.put(goodsImportDTOSku.getSkuKey(), goodsImportDTOSku.getSkuValue());
                    skuMap.put("price", goodsImportDTOSku.getPrice());
                    skuMap.put("quantity", goodsImportDTOSku.getQuantity());
                    skuMap.put("sn", goodsImportDTOSku.getSn());
                    skuMap.put("weight", goodsImportDTOSku.getWeight());
                    skuMap.put("cost", goodsImportDTOSku.getCost());
                    skuMap.put("images", goodsImportDTOSku.getImages());
                    skuList.add(skuMap);
                }
                goodsOperationDTO.setSkuList(skuList);

                Category parentCategory = categoryService.getCategoryById(goodsImportDTO.getCategory().getParentId());
                goodsOperationDTO.setCategoryPath(parentCategory.getParentId() + "," + parentCategory.getId() + "," + goodsImportDTO.getCategory().getParentId());
                //添加商品
                goodsIntegrationService.saveGoods(goodsOperationDTO);
            }
        }
    }

    /**
     * 添加商品
     *
     * @param goodsImportDTOList 商品列表
     */
    private void addSupplierGoodsList(List<GoodsImportDTO> goodsImportDTOList) {
        Map<String, List<GoodsImportDTO>> collect = goodsImportDTOList.stream().collect(Collectors.groupingBy(GoodsImportDTO::getGoodsName));
        for (Map.Entry<String, List<GoodsImportDTO>> entry : collect.entrySet()) {
            if (!entry.getValue().isEmpty()) {
                GoodsImportDTO goodsImportDTO = entry.getValue().get(0);
                SupplierGoodsOperationDTO goodsOperationDTO = new SupplierGoodsOperationDTO(entry.getValue().get(0));
                List<Map<String, Object>> skuList = new ArrayList<>();
                for (GoodsImportDTO goodsImportDTOSku : entry.getValue()) {
                    Map<String, Object> skuMap = new HashMap<>();
                    if (!skuList.isEmpty() && skuList.stream().noneMatch(sku -> sku.containsKey(goodsImportDTOSku.getSkuKey()))) {
                        for (Map<String, Object> map : skuList) {
                            map.keySet().stream().filter(key -> !key.equals("quantity") && !key.equals("sn") && !key.equals("weight") && !key.equals("cost") && !key.equals("images")).forEach(key -> skuMap.put(key, map.get(key)));
                            map.put(goodsImportDTOSku.getSkuKey(), goodsImportDTOSku.getSkuValue());
                        }
                    }
                    skuMap.put(goodsImportDTOSku.getSkuKey(), goodsImportDTOSku.getSkuValue());
                    skuMap.put("quantity", goodsImportDTOSku.getQuantity());
                    skuMap.put("sn", goodsImportDTOSku.getSn());
                    skuMap.put("weight", goodsImportDTOSku.getWeight());
                    if (goodsImportDTOSku.getWholesaleList() == null) {
                        skuMap.put("cost", goodsImportDTOSku.getCost());
                        skuMap.put("price", goodsImportDTOSku.getPrice());
                    }
                    skuMap.put("images", goodsImportDTOSku.getImages());
                    skuList.add(skuMap);
                }
                goodsOperationDTO.setSkuList(skuList);

                Category parentCategory = categoryService.getCategoryById(goodsImportDTO.getCategory().getParentId());
                goodsOperationDTO.setCategoryPath(parentCategory.getParentId() + "," + parentCategory.getId() + "," + goodsImportDTO.getCategory().getParentId());
                //添加商品
                goodsIntegrationService.saveSupplierGoods(goodsOperationDTO);
            }
        }
    }

    /**
     * 表格
     *
     * @param workbook       表格
     * @param sheetName      sheet名称
     * @param sheetNameIndex 开始
     * @param sheetData      数据
     * @param firstRow       开始行
     * @param lastRow        结束行
     * @param firstCol       开始列
     * @param lastCol        结束列
     */
    private void excelTo255(Workbook workbook, String sheetName, int sheetNameIndex, String[] sheetData,
                            int firstRow, int lastRow, int firstCol, int lastCol) {
        //将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据
        Sheet hidden = workbook.createSheet(sheetName);

        //创建单元格对象
        Cell cell;
        //遍历我们上面的数组，将数据取出来放到新sheet的单元格中
        for (int i = 0, length = sheetData.length; i < length; i++) {
            //取出数组中的每个元素
            String name = sheetData[i];
            //根据i创建相应的行对象（说明我们将会把每个元素单独放一行）
            Row row = hidden.createRow(i);
            //创建每一行中的第一个单元格
            cell = row.createCell(0);
            //然后将数组中的元素赋值给这个单元格
            cell.setCellValue(name);
        }
        // 创建名称，可被其他单元格引用
        Name namedCell = workbook.createName();
        namedCell.setNameName(sheetName);
        // 设置名称引用的公式
        namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + (sheetData.length > 0 ? sheetData.length : 1));
        //加载数据,将名称为hidden的sheet中的数据转换为List形式
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

        // 设置第一列的3-65534行为下拉列表
        // (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列)
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 将设置下拉选的位置和数据的对应关系 绑定到一起
        DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

        //将第二个sheet设置为隐藏
        workbook.setSheetHidden(sheetNameIndex, true);
        //将数据赋给下拉列表
        workbook.getSheetAt(0).addValidationData(dataValidation);
    }
}
